package com.zerowidth.db;

import com.zerowidth.db.base.SqlResult;
import com.zerowidth.db.utils.IOUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBHelper {

    private static final int FIRST_COLUMN_INDEX = 1;

    public static ResultSet executeQuery(String sql) {
        try {
            Statement statement = DBConn.getInst().getStmt();
            ResultSet rs = statement.executeQuery(sql);

            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public static SqlResult executeQueryEx(String sql) {
        try {
            SqlResult bean = new SqlResult();
            bean.stat = DBConn.getInst().getStmt();
            bean.rs = bean.stat.executeQuery(sql);

            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public static int executeUpdate(String sql) {
        Statement statement = null;
        try {
            statement = DBConn.getInst().getStmt();
            int count = statement.executeUpdate(sql);
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(statement);
        }

        return 0;
    }

    /**
     * 插入数据，并返回自增的id
     */
    public static int executeInsert(String sql) {
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            stat = DBConn.getInst().getCoon().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stat.executeUpdate();
            rs = stat.getGeneratedKeys();
            if (rs == null) {
                return -1;
            }
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            IOUtils.close(stat);
            IOUtils.close(rs);
        }

        return -1;
    }

    public static int queryCount(String sql) {
        SqlResult sr = DBHelper.executeQueryEx(sql);
        if (sr == null || sr.rs == null) {
            return 0;
        }
        int count = 0;
        try {
            if (sr.rs.next()) {
                count = sr.rs.getInt(FIRST_COLUMN_INDEX);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return count;
    }

    public static float queryFloat(String sql) {
        SqlResult sr = DBHelper.executeQueryEx(sql);
        if (sr == null || sr.rs == null) {
            return 0;
        }
        float count = 0;
        try {
            if (sr.rs.next()) {
                count = sr.rs.getFloat(FIRST_COLUMN_INDEX);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }
        return count;
    }

}
